package com.jackstraw.dbtool.utils;

import com.jackstraw.dbtool.exception.MyException;
import com.jackstraw.dbtool.param.CommonParam;
import com.jackstraw.dbtool.param.DBParam;
import com.jackstraw.dbtool.pojo.DBConnectData;
import com.jackstraw.dbtool.pojo.FieldData;
import com.jackstraw.dbtool.pojo.TableData;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 所在包：com.jackstraw.createcode.utils
 * 文件名：
 * 版权：稻草人.
 * 项目：createcode
 * 功能描述：
 * 修改人：Xinglz
 * 修改时间：2020-01-09 16:14
 */
public class DBUtil {

    /**
     * 数据库连接对象
     */
    private static Connection conn = null;

    /**
     * 数据库连接数据
     */
    // 数据库连接信息对象
    private static DBConnectData dbConnectData;
    // 数据库连接路径
    private static String connectPath = "";
    // 数据库连接地址
    private static String url = "";
    // 数据库端口
    private static String port = "";
    // 数据库名称
    private static String dbName = "";
    // 数据库用户名
    private static String username = "";
    // 数据库密码
    private static String password = "";

    /**
     * 初始化数据库连接
     * @param type 数据库类型
     * @param url 地址
     * @param port 端口
     * @param username 用户名
     * @param password 密码
     */
    public static void initConnection(String type, String url, String port, String dbName , String username, String password) throws Exception {
        // 初始化数据库连接信息
        if (dbConnectData == null) {
            dbConnectData = new DBConnectData();
        }
        dbConnectData.setType(type);
        dbConnectData.setUrl(url);
        dbConnectData.setPort(port);
        dbConnectData.setDbName(dbName);
        dbConnectData.setUsername(username);
        dbConnectData.setPassword(password);
        // 初始化数据库数据
        DBUtil.url = url;
        DBUtil.port = port;
        DBUtil.dbName = dbName;
        DBUtil.username = username;
        DBUtil.password = password;
        switch (type) {
            case DBParam.MYSQL:
                initMysqlConnectPath();
                break;
            case DBParam.ORACLE:
                initOracleConnectPath();
                break;
            case DBParam.SQLSERVER:
                initSqlServerConnectPath();
                break;
            default:
                throw new RuntimeException("当前数据库未添加到兼容！");
        }
        // 如果存在已有连接，先关闭连接
        if (conn != null)
            conn.close();
        // 初始化数据库连接
        conn = DriverManager.getConnection(connectPath, username, password);
    }

    /**
     * 初始化mysql数据库连接路径
     */
    private static void initMysqlConnectPath() {
        connectPath = "jdbc:mysql://"+url+":"+port+"/"+dbName+"?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        System.out.println("mysql: "+connectPath);
    }

    /**
     * 初始化oracle数据库连接路径
     */
    private static void initOracleConnectPath() {
        connectPath = "jdbc:oracle://"+url+":"+port+"/"+dbName+"?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        System.out.println("oracle: "+ connectPath);
    }

    /**
     * 初始化sqlserver数据库连接路径
     */
    private static void initSqlServerConnectPath() {
        connectPath = "jdbc:sqlserver://"+url+":"+port+";databaseName="+dbName;
        System.out.println("sqlserver: " + connectPath);
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConn() {
        if (conn == null) {
            throw new MyException("数据库连接失效，请重新连接！");
        }
        return conn;
    }

    /**
     * 获取数据库连接信息数据
     * @return
     */
    public static DBConnectData getDbConnectData() {
        return dbConnectData;
    }

    /**
     * 获取数据库表列表
     * @return
     */
    public static List<TableData> getTableList() throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        List<TableData> list = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet tables = metaData.getTables(dbName.toUpperCase(), "%", "%", new String[]{"TABLE"});
        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            String remark = tables.getString("REMARKS");
            TableData tableData = new TableData();
            tableData.setTableName(tableName);
            tableData.setRemark(remark);
            list.add(tableData);
        }
        return list;
    }

    /**
     * 获取表对应的字段信息
     * @param tableName 数据库表名
     * @return
     * @throws Exception
     */
    public static Map<String, String> getColumnToTypeMap(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        Map<String, String> map = new LinkedHashMap<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet columns = metaData.getColumns(dbName.toUpperCase(), null, tableName.toUpperCase(), null);
        while (columns.next()) {
            String columnName = columns.getString("COLUMN_NAME");
            String typeName = columns.getString("TYPE_NAME");
            map.put(columnName, typeName);
        }
        return map;
    }

    /**
     * 获取字段对应的注释map
     * @param tableName
     * @return
     * @throws Exception
     */
    public static Map<String, String> getColumnToRemarkMap(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        Map<String, String> map = new LinkedHashMap<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet columns = metaData.getColumns(dbName.toUpperCase(), null, tableName.toUpperCase(), null);
        while (columns.next()) {
            String columnName = columns.getString("COLUMN_NAME");
            String remarks = columns.getString("REMARKS");
            map.put(columnName, remarks);
        }
        return map;
    }

    /**
     * 根据表名获取字段信息
     * @param tableName
     * @return
     * @throws Exception
     */
    public static Map<String, FieldData> getFieldMap(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        Map<String, FieldData> map = new LinkedHashMap<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet columns = metaData.getColumns(dbName.toUpperCase(), null, tableName.toUpperCase(), null);
        while (columns.next()) {
            FieldData fieldData = new FieldData();
            String columnName = columns.getString("COLUMN_NAME");
            String typeName = columns.getString("TYPE_NAME");
            String columnSize = columns.getString("COLUMN_SIZE");
            String nullable = columns.getString("NULLABLE");
            String remarks = columns.getString("REMARKS");
            fieldData.setColumnName(columnName);
            fieldData.setTypeName(typeName);
            fieldData.setColumnSize(columnSize);
            fieldData.setNullAble(nullable);
            fieldData.setRemarks(remarks);
            map.put(columnName, fieldData);
        }
        return map;
    }

    /**
     * 根据表名获取字段信息
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<FieldData> getFieldList(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        List<FieldData> list = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet columns = metaData.getColumns(dbName.toUpperCase(), null, tableName.toUpperCase(), null);
        while (columns.next()) {
            FieldData fieldData = new FieldData();
            String columnName = columns.getString("COLUMN_NAME");
            String typeName = columns.getString("TYPE_NAME");
            String columnSize = columns.getString("COLUMN_SIZE");
            String nullable = columns.getString("NULLABLE");
            String remarks = columns.getString("REMARKS");
            String defaultValue = columns.getString("COLUMN_DEF");
            fieldData.setColumnName(columnName);
            fieldData.setTypeName(typeName);
            fieldData.setColumnSize(columnSize);
            fieldData.setNullAble(nullable);
            fieldData.setRemarks(remarks);
            fieldData.setDefaultValue(defaultValue);
            list.add(fieldData);
        }
        return list;
    }

    /**
     * 获取数据库主键字段
     * @param tableName
     * @return
     */
    public static String getPrimaryKey(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog().toUpperCase(),null, tableName.toUpperCase());
        String primaryKeyName = "";
        while (rs.next()) {
            primaryKeyName = rs.getString("COLUMN_NAME");
            if (MyStringUtil.isNotEmty(primaryKeyName)) {
                break;
            }
        }
        if (primaryKeyName == null) {
            System.out.println(CommonParam.LOG_REMARK_PREFIX + "当前数据库表并无主键，表名：" + tableName);
            return null;
        }
        return primaryKeyName;
    }

    /**
     * 获取数据库主键字段列表
     * @param tableName
     * @return
     */
    public static List<String> getPrimaryKeyList(String tableName) throws Exception {
        if (conn == null)
            throw new RuntimeException("当前数据库连接异常，请重新连接！");
        ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog().toUpperCase(),null, tableName.toUpperCase());
        List<String> primaryKeyList = new ArrayList<>();
        while (rs.next()) {
            String primaryKeyName = rs.getString("COLUMN_NAME");
            primaryKeyList.add(primaryKeyName);
        }
        return primaryKeyList;
    }
}
